from flask import Flask, render_template, send_from_directory
from flask_sqlalchemy import SQLAlchemy
from dotenv import load_dotenv
import os
import sys
from datetime import datetime

# 加载.env文件中的环境变量
load_dotenv()

app = Flask(__name__, template_folder='templates', static_folder='static')

# 数据库配置
# 优先使用环境变量中指定的SQLALCHEMY_DATABASE_URI
# 如果没有指定，则根据其他环境变量构建MySQL URI
# 如果设置了DB_NAME为*.db文件，则使用SQLite
SQLALCHEMY_DATABASE_URI = os.environ.get('SQLALCHEMY_DATABASE_URI')
if SQLALCHEMY_DATABASE_URI:
    app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
else:
    DB_USER = os.environ.get('DB_USER', 'root')
    DB_PASSWORD = os.environ.get('DB_PASSWORD', 'password')
    DB_HOST = os.environ.get('DB_HOST', 'localhost')
    DB_NAME = os.environ.get('DB_NAME', 'sdls_db')
    
    if DB_NAME.endswith('.db'):
        # 使用SQLite
        app.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{DB_NAME}'
    else:
        # 使用MySQL
        app.config['SQLALCHEMY_DATABASE_URI'] = f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}'

# 配置指标数据库连接
METRICS_DB_USER = os.environ.get('METRICS_DB_USER', 'root')
METRICS_DB_PASSWORD = os.environ.get('METRICS_DB_PASSWORD', '123456')
METRICS_DB_HOST = os.environ.get('METRICS_DB_HOST', 'localhost')
METRICS_DB_NAME = os.environ.get('METRICS_DB_NAME', 'sdls_zb_database')

app.config['SQLALCHEMY_BINDS'] = {
    'metrics': f'mysql+pymysql://{METRICS_DB_USER}:{METRICS_DB_PASSWORD}@{METRICS_DB_HOST}/{METRICS_DB_NAME}'
}

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 初始化数据库
db = SQLAlchemy(app)

# 定义模型
class Book(db.Model):
    """图书信息模型"""
    __tablename__ = 'books'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False, comment='书名')
    isbn = db.Column(db.String(20), unique=True, nullable=True, comment='ISBN')
    authors = db.Column(db.String(200), nullable=True, comment='作者')
    publisher = db.Column(db.String(100), nullable=True, comment='出版社')
    publish_date = db.Column(db.String(50), nullable=True, comment='出版日期')
    price = db.Column(db.String(20), nullable=True, comment='定价')
    hot_index = db.Column(db.Float, nullable=True, comment='热销指数')
    cover_image = db.Column(db.String(200), nullable=True, comment='封面图片路径')
    description = db.Column(db.Text, nullable=True, comment='内容描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<Book {self.title}>'

class Publisher(db.Model):
    """出版社信息模型"""
    __tablename__ = 'publishers'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False, comment='出版社名称')
    address = db.Column(db.String(200), nullable=True, comment='地址')
    contact = db.Column(db.String(50), nullable=True, comment='联系方式')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<Publisher {self.name}>'

class SalesData(db.Model):
    """销售数据模型"""
    __tablename__ = 'sales_data'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    publisher_id = db.Column(db.Integer, db.ForeignKey('publishers.id'), nullable=True, comment='出版社ID')
    quantity = db.Column(db.Integer, nullable=False, default=0, comment='销售数量')
    batch_count = db.Column(db.Integer, nullable=True, comment='批次')
    sales_date = db.Column(db.Date, nullable=True, comment='销售日期')
    amount = db.Column(db.Float, nullable=True, comment='销售金额')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('sales', lazy=True))
    publisher = db.relationship('Publisher', backref=db.backref('sales', lazy=True))
    
    def __repr__(self):
        return f'<SalesData Book:{self.book_id} Quantity:{self.quantity}>'

class Inventory(db.Model):
    """库存数据模型"""
    __tablename__ = 'inventory'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    quantity = db.Column(db.Integer, nullable=False, default=0, comment='库存数量')
    location = db.Column(db.String(100), nullable=True, comment='库存位置')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('inventory', uselist=False))
    
    def __repr__(self):
        return f'<Inventory Book:{self.book_id} Quantity:{self.quantity}>'

class Review(db.Model):
    """用户评论模型"""
    __tablename__ = 'reviews'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    username = db.Column(db.String(50), nullable=False, comment='用户名')
    content = db.Column(db.Text, nullable=False, comment='评论内容')
    likes = db.Column(db.Integer, default=0, comment='点赞数')
    comments = db.Column(db.Integer, default=0, comment='评论数')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('reviews', lazy=True))
    
    def __repr__(self):
        return f'<Review Book:{self.book_id} User:{self.username}>'

class Logistics(db.Model):
    """物流信息模型"""
    __tablename__ = 'logistics'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    publisher_id = db.Column(db.Integer, db.ForeignKey('publishers.id'), nullable=True, comment='出版社ID')
    status = db.Column(db.String(20), nullable=False, comment='物流状态')  # 入库, 捡货, 出库, 运输, 签收
    location = db.Column(db.String(100), nullable=True, comment='当前位置')
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')
    details = db.Column(db.Text, nullable=True, comment='详细信息')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('logistics', lazy=True))
    publisher = db.relationship('Publisher', backref=db.backref('logistics', lazy=True))
    
    def __repr__(self):
        return f'<Logistics Book:{self.book_id} Status:{self.status}>'

class BookCategory(db.Model):
    """图书分类模型"""
    __tablename__ = 'book_categories'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    category = db.Column(db.String(50), nullable=False, comment='分类')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('categories', lazy=True))
    
    def __repr__(self):
        return f'<BookCategory Book:{self.book_id} Category:{self.category}>'

class BookTag(db.Model):
    """图书标签模型"""
    __tablename__ = 'book_tags'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False, comment='图书ID')
    tag = db.Column(db.String(50), nullable=False, comment='标签')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    # 关联关系
    book = db.relationship('Book', backref=db.backref('tags', lazy=True))
    
    def __repr__(self):
        return f'<BookTag Book:{self.book_id} Tag:{self.tag}>'

# 指标数据模型
class DailySalesSummary(db.Model):
    """每日销售汇总指标"""
    __tablename__ = 'daily_sales_summary'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Date, nullable=False, comment='统计日期')
    total_quantity = db.Column(db.Integer, nullable=False, default=0, comment='当日总销量')
    total_amount = db.Column(db.Float, nullable=False, default=0.0, comment='当日销售总额')
    book_count = db.Column(db.Integer, nullable=False, default=0, comment='涉及图书种类数')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<DailySalesSummary Date:{self.date} Quantity:{self.total_quantity}>'

class BookSalesRanking(db.Model):
    """图书销售排行榜指标"""
    __tablename__ = 'book_sales_ranking'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, nullable=False, comment='图书ID')
    ranking_period = db.Column(db.String(20), nullable=False, comment='排行周期')  # daily, weekly, monthly, yearly
    ranking_value = db.Column(db.Integer, nullable=False, comment='排名')
    sales_quantity = db.Column(db.Integer, nullable=False, default=0, comment='销售数量')
    sales_amount = db.Column(db.Float, nullable=False, default=0.0, comment='销售金额')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<BookSalesRanking Book:{self.book_id} Period:{self.ranking_period} Rank:{self.ranking_value}>'


class CategorySalesSummary(db.Model):
    """分类销售汇总指标"""
    __tablename__ = 'category_sales_summary'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(50), nullable=False, comment='分类')
    sales_quantity = db.Column(db.Integer, nullable=False, default=0, comment='销售数量')
    sales_amount = db.Column(db.Float, nullable=False, default=0.0, comment='销售金额')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<CategorySalesSummary Category:{self.category} Amount:{self.sales_amount}>'


class InventoryTurnover(db.Model):
    """库存周转率指标"""
    __tablename__ = 'inventory_turnover'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    book_id = db.Column(db.Integer, nullable=False, comment='图书ID')
    turnover_rate = db.Column(db.Float, nullable=False, comment='周转率')
    sales_quantity = db.Column(db.Integer, nullable=False, default=0, comment='销售数量')
    average_inventory = db.Column(db.Integer, nullable=False, comment='平均库存')
    statistic_period = db.Column(db.String(20), nullable=False, comment='统计周期')  # monthly, quarterly
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<InventoryTurnover Book:{self.book_id} Rate:{self.turnover_rate}>'


# 新增指标表
class PublisherSalesRanking(db.Model):
    """出版社销售排行指标"""
    __tablename__ = 'publisher_sales_ranking'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    publisher_id = db.Column(db.Integer, nullable=False, comment='出版社ID')
    ranking_period = db.Column(db.String(20), nullable=False, comment='排行周期')  # daily, weekly, monthly, yearly
    ranking_value = db.Column(db.Integer, nullable=False, comment='排名')
    total_sales_quantity = db.Column(db.Integer, nullable=False, default=0, comment='总销售数量')
    total_sales_amount = db.Column(db.Float, nullable=False, default=0.0, comment='总销售金额')
    book_count = db.Column(db.Integer, nullable=False, default=0, comment='图书种类数')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<PublisherSalesRanking Publisher:{self.publisher_id} Period:{self.ranking_period} Rank:{self.ranking_value}>'

class CategoryInventorySummary(db.Model):
    """分类库存汇总指标"""
    __tablename__ = 'category_inventory_summary'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(50), nullable=False, comment='分类')
    total_quantity = db.Column(db.Integer, nullable=False, default=0, comment='总库存数量')
    total_value = db.Column(db.Float, nullable=False, default=0.0, comment='总库存价值')
    book_count = db.Column(db.Integer, nullable=False, default=0, comment='图书种类数')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<CategoryInventorySummary Category:{self.category} Quantity:{self.total_quantity}>'

class RegionalSalesSummary(db.Model):
    """地区销售汇总指标"""
    __tablename__ = 'regional_sales_summary'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    region = db.Column(db.String(50), nullable=False, comment='地区')
    total_sales_quantity = db.Column(db.Integer, nullable=False, default=0, comment='总销售数量')
    total_sales_amount = db.Column(db.Float, nullable=False, default=0.0, comment='总销售金额')
    book_count = db.Column(db.Integer, nullable=False, default=0, comment='图书种类数')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<RegionalSalesSummary Region:{self.region} Amount:{self.total_sales_amount}>'

class DocumentStatistics(db.Model):
    """电子单证统计数据"""
    __tablename__ = 'document_statistics'
    __bind_key__ = 'metrics'
    
    id = db.Column(db.Integer, primary_key=True)
    document_type = db.Column(db.String(50), nullable=False, comment='单据类型')  # 新书书目、采购单、发货单、收货单、书目变更单、可用库存
    count = db.Column(db.Integer, nullable=False, default=0, comment='数量')
    statistic_date = db.Column(db.Date, nullable=False, comment='统计日期')
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    
    def __repr__(self):
        return f'<DocumentStatistics Type:{self.document_type} Count:{self.count}>'

def seed_database():
    """填充示例数据"""
    # 检查是否已有数据
    if Book.query.first() is not None:
        print("数据库中已有数据，跳过填充")
        return

    # 创建出版社数据
    publishers_data = [
        {"name": "人民出版社", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "商务印书馆有限公司", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "学习出版社", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "南海出版公司", "address": "海南省海口市", "contact": "0898-xxxxxxx"},
        {"name": "贵州人民出版社", "address": "贵州省贵阳市", "contact": "0851-xxxxxxx"},
        {"name": "上海人民出版社", "address": "上海市", "contact": "021-xxxxxxx"},
        {"name": "高等教育出版社", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "中华书局", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "中国人民大学出版社", "address": "北京市", "contact": "010-xxxxxxx"},
        {"name": "社会科学文献出版社", "address": "北京市", "contact": "010-xxxxxxx"}
    ]
    
    publishers = []
    for pub_data in publishers_data:
        publisher = Publisher(**pub_data)
        db.session.add(publisher)
        publishers.append(publisher)
    
    db.session.commit()
    print(f"已创建 {len(publishers)} 个出版社")

    # 创建图书数据
    books_data = [
        {
            "title": "党的十九19大报告辅导读本+中国共产党章程 全2册",
            "isbn": "9787010184272",
            "authors": "中共中央宣传部",
            "publish_date": "2017年10月",
            "price": "￥37.00",
            "hot_index": 9.85
        },
        {
            "title": "中国党第十九次全国代表大会文件汇编",
            "isbn": "9787010184326",
            "authors": "中共中央宣传部",
            "publish_date": "2017年10月",
            "price": "￥12.00",
            "hot_index": 9.83
        },
        {
            "title": "全面从严治党面对面",
            "isbn": "9787514707663",
            "authors": "中共中央宣传部理论局",
            "publish_date": "2017年03月",
            "price": "￥19.80",
            "hot_index": 9.68
        },
        {
            "title": "现代汉语词典(第7版)",
            "isbn": "9787100124508",
            "authors": "中国社会科学院语言研究所词典编辑室",
            "publish_date": "2016年09月",
            "price": "￥109.00",
            "hot_index": 9.66
        },
        {
            "title": "解忧杂货店",
            "isbn": "9787544270878",
            "authors": "(日)东野圭吾",
            "publish_date": "2014年05月",
            "price": "￥39.50",
            "hot_index": 9.65
        },
        {
            "title": "追风筝的人",
            "isbn": "9787208061644",
            "authors": "[美]卡勒德·胡赛尼",
            "publish_date": "2006年05月",
            "price": "￥29.00",
            "hot_index": 9.62
        },
        {
            "title": "天才在左疯子在右（完整版）",
            "isbn": "9787550263932",
            "authors": "高铭",
            "publish_date": "2016年01月",
            "price": "￥39.80",
            "hot_index": 9.59
        },
        {
            "title": "新华字典（第11版）（双色本）",
            "isbn": "9787100077040",
            "authors": "中国社会科学院语言研究所",
            "publish_date": "2011年06月",
            "price": "￥24.90",
            "hot_index": 9.58
        },
        {
            "title": "神奇校车·图画书版（全11册）",
            "isbn": "21005473",
            "authors": "(美)乔安娜柯尔",
            "publish_date": "2011年01月",
            "price": "￥132.00",
            "hot_index": 9.56
        },
        {
            "title": "这就是二十四节气",
            "isbn": "9787511026118",
            "authors": "高春香，邵敏",
            "publish_date": "2015年09月",
            "price": "￥100.00",
            "hot_index": 9.53
        }
    ]
    
    books = []
    for book_data in books_data:
        book = Book(**book_data)
        db.session.add(book)
        books.append(book)
    
    db.session.commit()
    print(f"已创建 {len(books)} 本图书")

    # 添加库存信息
    for i, book in enumerate(books):
        inventory = Inventory(
            book_id=book.id,
            quantity=1000 - i * 50  # 模拟不同库存量
        )
        db.session.add(inventory)
    
    # 添加分类信息
    categories = ["政治", "文学", "教育", "儿童", "工具书", "小说"]
    for i, book in enumerate(books):
        category = BookCategory(
            book_id=book.id,
            category=categories[i % len(categories)]
        )
        db.session.add(category)
    
    # 添加标签
    tags_data = [
        ["政治", "理论", "学习"],
        ["政治", "文件", "十九大"],
        ["政治", "党建", "从严治党"],
        ["工具书", "汉语", "词典"],
        ["小说", "外国文学", "治愈"],
        ["小说", "外国文学", "经典"],
        ["心理学", "精神病学", "故事"],
        ["工具书", "汉语", "字典"],
        ["儿童", "科普", "图画书"],
        ["儿童", "传统文化", "节气"]
    ]
    
    for i, book in enumerate(books):
        for tag_name in tags_data[i]:
            tag = BookTag(
                book_id=book.id,
                tag=tag_name
            )
            db.session.add(tag)
    
    # 添加示例指标数据
    # 添加每日销售汇总示例数据
    from datetime import date, timedelta
    today = date.today()
    for i in range(1, 8):
        sales_date = today - timedelta(days=i)
        daily_summary = DailySalesSummary(
            date=sales_date,
            total_quantity=1000 - i * 50,
            total_amount=20000.0 - i * 1000.0,
            book_count=10
        )
        db.session.add(daily_summary)
    
    # 添加图书销售排行示例数据
    for i, book in enumerate(books[:5]):
        ranking = BookSalesRanking(
            book_id=book.id,
            ranking_period="weekly",
            ranking_value=i+1,
            sales_quantity=200 - i * 20,
            sales_amount=4000.0 - i * 400.0,
            statistic_date=today
        )
        db.session.add(ranking)
    
    # 添加分类销售汇总示例数据
    for i, category in enumerate(categories[:5]):
        category_summary = CategorySalesSummary(
            category=category,
            sales_quantity=300 - i * 30,
            sales_amount=6000.0 - i * 600.0,
            statistic_date=today
        )
        db.session.add(category_summary)
    
    # 添加库存周转率示例数据
    for i, book in enumerate(books[:5]):
        turnover = InventoryTurnover(
            book_id=book.id,
            turnover_rate=1.5 - i * 0.1,
            sales_quantity=150 - i * 15,
            average_inventory=100,
            statistic_period="monthly",
            statistic_date=today
        )
        db.session.add(turnover)
    
    # 添加出版社销售排行示例数据
    for i, publisher in enumerate(publishers[:5]):
        publisher_ranking = PublisherSalesRanking(
            publisher_id=publisher.id,
            ranking_period="monthly",
            ranking_value=i+1,
            total_sales_quantity=500 - i * 50,
            total_sales_amount=10000.0 - i * 1000.0,
            book_count=2,
            statistic_date=today
        )
        db.session.add(publisher_ranking)
    
    # 添加分类库存汇总示例数据
    for i, category in enumerate(categories[:5]):
        category_inventory = CategoryInventorySummary(
            category=category,
            total_quantity=800 - i * 80,
            total_value=15000.0 - i * 1500.0,
            book_count=2,
            statistic_date=today
        )
        db.session.add(category_inventory)
    
    # 添加地区销售汇总示例数据
    regions = ["北京", "上海", "广州", "深圳", "杭州"]
    for i, region in enumerate(regions):
        regional_summary = RegionalSalesSummary(
            region=region,
            total_sales_quantity=400 - i * 40,
            total_sales_amount=8000.0 - i * 800.0,
            book_count=10,
            statistic_date=today
        )
        db.session.add(regional_summary)
    
    db.session.commit()
    print("数据填充完成!")

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/second')
@app.route('/second.html')
def second():
    return render_template('second.html')

@app.route('/third')
@app.route('/third.html')
def third():
    return render_template('third.html')

@app.route('/four')
@app.route('/four.html')
def four():
    return render_template('four.html')

@app.route('/static/<path:filename>')
def custom_static(filename):
    return send_from_directory(os.path.join(app.root_path, 'static'), filename)

if __name__ == '__main__':
    with app.app_context():
        try:
            # 创建所有表
            db.create_all()
            print("数据库连接成功，表结构已创建或已存在")
            print(f"数据库URI: {app.config['SQLALCHEMY_DATABASE_URI']}")
            
            # 填充示例数据
            seed_database()
        except Exception as e:
            print(f"数据库连接失败: {e}")
            print("请检查您的数据库配置是否正确，或在 .env 文件中设置正确的数据库连接信息")
            print("如需使用 SQLite 数据库，请在 .env 文件中设置 DB_NAME=app.db")
            sys.exit(1)
    app.run()